​x

<font size="+3"><strong>Databases: SQL</strong></font>

from IPython.display import YouTubeVideo

# Working with SQL Databases

A database is a collection of interrelated data. The primary goal of a database is to store and retrieve information in a convenient and efficient way. There are many types of databases. In this section, we will be dealing with a **relational database**. A relational database is a widely used database model that consists of a collection of uniquely named **tables** used to store information. The structure of a database model with its tables, constraints, and relationships is called a **schema**.

# Connecting to a Database

## ipython-sql

### Magic Commands

Jupyter notebooks can run code that is not valid Python code but still affect the notebook . These special commands are called magic commands. Magic commands can have a range of properties. Some commonly used magic functions are below:

### ipython-sql

`ipython-sql` allows you to write SQL code directly in a Jupyter Notebook. The `%sql` (or `%%sql`) magic command is added to the beginning of a code block and then SQL code can be written.

### Connecting with ipython-sql

We can connect to a database using the %sql magic function:

%sql sqlite:////home/jovyan/nepal.sqlite

## sqlite3

We can also connect to the same database using the sqlite3 package:

conn = sqlite3.connect("/home/jovyan/nepal.sqlite")

# Querying a Database

## Building Blocks of the Basic Query

There are six common clauses used for querying data:

## SELECT and FROM

You can use `SELECT *` to select all columns in a table. `FROM` specifies the table in the database to query. `LIMIT 5` will select only the first five rows.

FROM id_map

You can also use `SELECT` to select certain columns in a table

SELECT household_id,

<font size="+1">Practice</font>

%%sql

We can also assign an **alias** or temporary name to a column using the `AS` command. Aliases can also be used on a table. See the example below, which assigns the alias `household_number` to `household_id`

SELECT household_id AS household_number,

<font size="+1">Practice</font>

%%sql

## Filtering and Sorting Data

SQL provides a variety of comparison operators that can be used with the WHERE clause to filter the data.

For example, to select the first 5 homes in Ramechhap (district `2`):

%%sql

<font size="+1">Practice</font>

%%sql

## Aggregating Data

Aggregation functions take a collection of values as inputs and return one value as the output. The table below gives the frequently used built-in aggregation functions:

Use the `COUNT` function to find the number of observations in the `id_map` table that come from Ramechhap (district `2`):

WHERE district_id = 2

Aggregation functions are frequently used with a `GROUP BY` clause to perform the aggregation on groups of data. For example, the query below returns the count of observations in each District:

GROUP BY district_id

 `DISTINCT` is a keyword to select unique records in a query result. For example, if we want to know the unique values in the `district_id` column:

SELECT distinct(district_id)

<font size="+1">Practice</font>

%%sql

`DISTINCT` and `COUNT` can be used in combination to count the number of distinct records. For example, if we want to know the number of unique values in the `district_id` column:

SELECT count(distinct(district_id))

<font size="+1">Practice</font>

%%sql

# Joining Tables

Joins link data from two or more tables together by using a column that is common between the two tables. The basic syntax for a join is below, where `table1` and `table2` refer to the two tables being joined, `column1` and `column2` refer to columns to be returned from both tables, and `ID` refers to the common column in the two tables.

We'll explore the concept of joins by first identifying a single household that we'd like to pull in building information for. For example, let's say we want to see the corresponding `foundation_type` for the first home in Ramechhap (District 1). We'll start by looking at this single record in the `id_map` table.

WHERE district_id = 2

This household has `building_id` equal to 23. Let's look at the `foundation_type` for this building, by filtering the `building_structure` table to find this building.

FROM building_structure

To join the two tables and limit the results to `building_id = 23`:

JOIN building_structure ON id_map.building_id = building_structure.building_id

In addition to the basic `JOIN` clause, specific join types can be specified, which specify whether the common column needs to be in one, both, or either of the two tables being joined. The different join types are below. The left table is the table specified first, immediately after the `FROM` clause and the right table is the table specified after the `JOIN` clause. If the generic `JOIN` clause is used, then by default the `INNER JOIN` will be used.

The video below outlines the main types of joins:

YouTubeVideo("2HVMiPPuPIM")

<font size="+1">Practice</font>

%%sql

# Using pandas with SQL Databases

To save the output of a query into a pandas DataFrame, we will use connect to the SQLite database using the SQLite3 package:

conn = sqlite3.connect("/home/jovyan/nepal.sqlite")

To run a query using `sqlite3`, we need to store the query as a string. For example, the variable below called `query` is a string containing a query which returns the first 10 rows from the `id_map` table:

    FROM id_map

To save the results of the query into a pandas DataFrame, use the `pd.read_sql()` function. The optional parameter `index_col` can be used to set the index to a specific column from the query.

df = pd.read_sql(query, conn, index_col="building_id")

<font size="+1">Practice</font>

query = ...

# References & Further Reading

---

​x

<font size="+3"><strong>Machine Learning: Linear Regression</strong></font>

# Linear Regression

xxxxxxxxxx from IPython.display import YouTubeVideo

In machine learning, a **regression** problem is when you need to build a model that's going to predict a continuous, numerical value, like the sale price of an apartment. One of the models that you can use for regression problems is called **linear regression**. In it's simplest form, we fit a model that will predict a single output variable (called a **target vector**) as a linear function of a single input variable (called a **feature matrix**).

## Fitting a Model to Training Data

You'll work on two cases: a model on the raw data set and a model on transformed data. First try to use linear regression to predict `price_aprox_usd` as a multiple of `surface_covered_in_m2` and the addition of a constant for the `mexico-city-real-estate-1.csv` dataset.<span style='color: transparent; font-size:1%'>WQU WorldQuant University Applied Data Science Lab QQQQ</span>

ximport pandas as pdfrom sklearn.linear_model import LinearRegression​# Import datacolumns = ["surface_covered_in_m2", "price_aprox_usd"]mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv", usecols=columns)​# Drop rows with missing values# (or you could use an imputer ☝️)mexico_city1.dropna(inplace=True)​# Split data into feature matrixX = mexico_city1[["surface_covered_in_m2"]]y = mexico_city1["price_aprox_usd"]​# Instantiate predictorlr = LinearRegression()​# Fit predictor to datalr.fit(X, y)

LinearRegression()

LinearRegression()

<font size="+1">Practice</font>

xxxxxxxxxx # Import datacolumns = ["price_aprox_usd", "surface_covered_in_m2"]mexico_city2 = pd.read_csv("./data/mexico-city-real-estate-2.csv", usecols=columns)# Drop rows with missing valuesmexico_city2.dropna(inplace=True)​# Split data into feature matrixX = mexico_city2[["surface_covered_in_m2"]]y = mexico_city2["price_aprox_usd"]​# Instantiate predictorlr = LinearRegression()​# Fit predictor to datalr.fit(X,y)​

LinearRegression()

LinearRegression()

## Generating Predictions Using a Trained Model

After fitting the model, we want to use it to make predictions. In most applications, you'll want to predict an unknown quantity from data that's different from the data you've fitted our model on. To test the accuracy of your fitted model, you'll typically use a different set of data with an outcome you already know. Here, we'll use the dataset from `mexico-city-test-features.csv` and `mexico-city-test-labels.csv`.  It's also helpful to plot the data and predicted data to see if there are any patterns that suggest fitting a different model.

x# Import datamexico_city_features = pd.read_csv(    "./data/mexico-city-test-features.csv", usecols=["surface_covered_in_m2"])mexico_city_labels = pd.read_csv("./data/mexico-city-test-labels.csv")​# Drop missing valuesmexico_city_features.dropna(inplace=True)​# Generate predictionsprice_pred_example = lr.predict(mexico_city_features)​# Print predictionsprice_pred_example[:5]

array([309549.84644749, 309411.49120101, 310207.03386826, 309428.78560682,
       309515.25763587])

<font size="+1">Practice</font>

xxxxxxxxxx # Import datamexico_city4 = pd.read_csv( "./data/mexico-city-test-features.csv",usecols=["surface_covered_in_m2"])​# Drop missing valuesmexico_city4.dropna(inplace=True)​# Generate predictionsprice_pred = lr.predict(mexico_city4)​# Print predictionsprice_pred[:5]

array([309549.84644749, 309411.49120101, 310207.03386826, 309428.78560682,
       309515.25763587])

## Ridge Regression

xxxxxxxxxx YouTubeVideo("Q81RR3yKn30")

## Generalization

Notice that we tested the model with a dataset that's *different* from the one we used to train the model. Machine learning models are useful if they allow you to make predictions about data other than what you used to train your model. We call this concept **generalization**.  By testing your model with different data than you used to train it, you're checking to see if your model can generalize.  Most machine learning models do not generalize to all possible types of input data, so they should be used with care. On the other hand, machine learning models that don't generalize to make predictions for at least a restricted set of data aren't very useful.

## Calculating the Mean Absolute Error for a List of Predictions

Plots are great for displaying information, but a value that tells you the typical error in a prediction is helpful too. This value is called the **mean absolute error**, and it's defined as the average value of the magnitude of the error in the predictions. The closer the MAE is to `0`, the better our model fits the data. scikit-learn will do this for you if you pass it the price predictions from your regression model and the actual prices from the test data set. Let's see how our `lr` model did by comparing its predictions to the true values in `mexico_city_labels`.

xxxxxxxxxx from sklearn.metrics import mean_absolute_error​mean_absolute_error(price_pred_example, mexico_city_labels)

226209.01327442465

## Access an Attribute of a Trained Model

After training a model that fits a straight line to your data, you can now obtain the parameters that fit your line. We're particularly interested in the slope `regr_lr.coef_` and the axis intercept `regr_lr.intercept_`

xxxxxxxxxx print(lr.coef_)

[3.45888116]

xxxxxxxxxx print(lr.intercept_)

309238.5471429155

## Multicollinearity

When you're creating a linear model that uses many features to make predictions, some of those features can be highly correlated with each other. This isn't a problem that's going to break your model; it will still make predictions and it might have good performance metrics. But it is an issue if you want to interpret the coefficients for your model because it becomes hard to tell which features are truly important.

xxxxxxxxxx import pandas as pdfrom sklearn.linear_model import LinearRegression​# Import datacolumns = [    "price",    "price_aprox_local_currency",    "price_aprox_usd",    "surface_total_in_m2",    "surface_covered_in_m2",    "price_per_m2",]mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv", usecols=columns)​# Drop missing valuesmexico_city1.dropna(inplace=True)​mexico_city1.head()

Now let's find the correlations between the columns.

xxxxxxxxxx mexico_city1.corr()

Let's see what happens when we fit a linear regression model for `surface_covered_in_m2` as a function of `price_aprox_usd` and `price_aprox_local_currency`.

xxxxxxxxxx lr = LinearRegression()lr.fit(    mexico_city1[["price_aprox_usd", "price_aprox_local_currency"]],    mexico_city1["surface_covered_in_m2"],)

LinearRegression()

LinearRegression()

Let's take a look at the coefficients of the model:

xxxxxxxxxx print(lr.coef_)

[6593.61513754 -350.56569568]

Ask yourself: Does it make sense that increasing the price of a property by one US dollar would translate to a 6593 m<sup>2</sup> increase in size? Perhaps, though it seems unlikely. And does it make sense that increasing the price by one Mexican peso would translate to a 350 m<sup>2</sup> *decrease* in size? Definitely not. So while this model may perform well when we evaluate it using metrics like mean absolute error, we can't use it to determine which features actually our target.

*References & Further Reading*

---

​x

<font size="+3"><strong>Machine Learning: Data Pre-Processing and Production</strong></font>

warnings.simplefilter(action="ignore", category=FutureWarning)

# What's scikit-learn?

# Data Preprocessing

# Standardization

**Standardization** is a widely used scaling technique to transform features before fitting into models. Feature scaling changes all a dataset's continuous features to give us a more consistent range of values. Specifically, we subtract the mean from each data point and then divide by the standard deviation:

Let's check the following example where we apply standardization on one of the columns in the following DataFrame:

df = pd.read_csv("./data/mexico-city-test-features.csv").dropna()

Our target feature is the `"surface_covered_in_m2"` column. Let's first check the maximum and minimum of this column before standardization:

print("Maximum before standardization is:", df["surface_covered_in_m2"].max())

Maximum before standardization is: 280.0
Minimum before standardization is: 50.0

We can perform the transformation by first instantiating the scaler and assigning the feature to a variable name. Then we fit the scaler and transform the data:

from sklearn.preprocessing import StandardScaler

# Fit the scaler to feature

StandardScaler()

StandardScaler()

# Pass the scaler to feature to transform data

array([[ 1.62304525e-01],
       [-1.11902808e+00],
       [ 6.24863439e+00],
       ...,
       [ 2.13794980e-03],
       [-3.18195201e-01],
       [ 2.13794980e-03]])

Now you can see the transformed data range is much smaller after standardization:

print("Maximum after standardization is:", X_transformed.max())

Maximum after standardization is: 6.248634385593622
Minimum after standardization is: -1.1190280771385155

We can also combine the fit and transform process together into one step:

X_transformed = scaler.fit_transform(X_train)

array([[ 1.62304525e-01],
       [-1.11902808e+00],
       [ 6.24863439e+00],
       ...,
       [ 2.13794980e-03],
       [-3.18195201e-01],
       [ 2.13794980e-03]])

<font size="+1">Practice</font>

df1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")

X_transformed = ...

Ellipsis

## One-Hot Encoding

A property's district is **categorical data**, or data which can be divided into groups.  For many machine learning algorithms, it's common to create a column in a DataFrame to indicate if the feature is present or absent, instead of using the category's name. First you a column for each district names then, for each observation, you put a 1 or a 0 to indicate if the property is located in each neighborhood or not. Let's take a look at the `mexico-city-test-features.csv` dataset for properties which include the district.

df = pd.read_csv("./data/mexico-city-test-features.csv").dropna()

You can do one-hot encoding using pandas [`get_dummies`](https://pandas.pydata.org/docs/reference/api/pandas.get_dummies.html) function, but we'll use a the [Category Encoders](https://contrib.scikit-learn.org/category_encoders/) library since it allows us to integrate the one hot encoder as a transformer in a scikit-learn Pipeline.

from category_encoders import OneHotEncoder

<font size="+1">Practice</font>

    "./data/mexico-city-real-estate-1.csv", usecols=["property_type"]

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In [13], line 6
      4 ohe = ...
      5 mexico_city1_ohe = ...
----> 6 mexico_city1_ohe.head()

AttributeError: 'ellipsis' object has no attribute 'head'

## Ordinal Encoding

For many machine learning algorithms, it's common to use one-hot encoding. This works well if there are a few categories, but as the number of features grows, the number of additional columns also grows.

from category_encoders import OrdinalEncoder

<font size="+1">Practice</font>

    "./data/mexico-city-real-estate-1.csv", usecols=["property_type"]

## Imputation

Let's take a look at `mexico-city-real-estate-1.csv` and impute some of the missing values. First, we'll load the dataset, limiting ourselves to the `"surface_covered_in_m2"` and `"price_aprox_usd"` columns.

mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv", usecols=columns)

When you need to build a model using features that contain missing values, one helpful tool is the scikit-learn transformer [`SimpleImputer`](https://scikit-learn.org/stable/modules/generated/sklearn.impute.SimpleImputer.html). In order to use it, we need to start by instantiating the transformer.

from sklearn.impute import SimpleImputer

Next, we train the imputer using the data. At this step it will calculate the mean value for each column.

imputer.fit(mexico_city1)

Last, we transform the data using the imputer.

mexico_city1_imputed = imputer.transform(mexico_city1)

Since the imputer doesn't return a DataFrame, let's transform it into one.

mexico_city1_imputed = pd.DataFrame(mexico_city1_imputed, columns=columns)

Now there are no missing values!

Then we use the imputer to transform the data.

mexico_city2_imputed = pd.DataFrame(mexico_city2_imputed, columns=columns)

## Data Leakage

Let's consider the `mexico-city-real-estate-1.csv` dataset and fit a regression model using `surface_covered_in_m2` and `price_aprox_local_currency` to estimate `price_aprox_usd`.

mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv", usecols=columns)

Now let's calculate the mean absolute error in our training data.

    mexico_city1[["surface_covered_in_m2", "price_aprox_local_currency"]]

When you see a mean absolute error that's so close to zero (especially when the mean apartment price is so much larger), chances are there is leakage in your model!

# Imbalanced Data

When dealing with classification problems, we would ideally expect the training data to be evenly spread across different classes for better model performance. When the numbers of observations are uneven in different classes, we have imbalanced data. The class that represents the majority of observations is called the **majority class**, while the class with limited observation is called the **minority class**. Imbalanced data limits training data available for certain classes. In addition, when the one class takes the majority of the data, the model will keep predicting the majority class to achieve high accuracy result. Thus, prior to training a  model, it is essential to balance the data either through under-sampling the majority classes, or over-sampling the minority classes, or use other evaluation metrics like **recall** or **precision**.

## Under-sampling

When data is imbalanced in different classes, one way we can balance it is reducing the number of observations in the majority class. This is called **under-sampling**. We can under-sample by randomly deleting some observations in the majority class. The open source [imbalanced-learn](https://imbalanced-learn.org/stable/) (imported as `imblearn`) is an open-source library that works with `scikit-learn` and provides tools when dealing with imbalanced classes. Here's an example of randomly deleting observations from the majority class using Poland bankruptcy data from 2008.

with gzip.open("data/poland-bankruptcy-data-2008.json.gz", "r") as f:

The data is clearly imbalanced as there are many more observations in non-bankruptcy compared to bankruptcy.

X, y = RandomUnderSampler().fit_resample(df[["company_id"]], df[["bankrupt"]])

Now we have reduced the non-bankruptcy class to the same size as the bankruptcy class.

## Over-sampling

**Over-sampling** is the opposite of under-sampling. Instead of reducing the majority class, over-sampling increases the number of observations in the minority class by randomly making copies of the existing observations. Here is an example of making random copies from the minority class using the Poland bankruptcy data and `imblearn`.

X, y = RandomOverSampler().fit_resample(df[["company_id"]], df[["bankrupt"]])

Now we have increased the bankruptcy class to the size of the non-bankruptcy class.

### Practice

Now that you've seen an example of imbalanced data and how to under-  or over-sample it prior to model training, let's get some practice with the Poland bankruptcy data from 2007.

with gzip.open("data/poland-bankruptcy-data-2007.json.gz", "r") as f:

First, check whether this data is imbalanced.

​x

Next, do under-sampling.

X, y = ...

Finally, check whether the data is balanced.

​x

Great work! Now try over-sampling.

X, y = ...

And check whether the data is balanced.

​x

# scikit-learn in Production

The previous examples have built models and made predictions one step at a time.  Many machine learning applications will require you to run the same steps many times, usually with new or updated data.  scikit-learn allows you to define a set of steps to process data for machine learning in a reproducible manner using a pipeline.

## Creating a Pipeline in scikit-learn

First, we create a pipeline to do linear regression on the transformed data set.

lin_reg = linear_model.LinearRegression()

We can check the steps in the pipeline, but right now, there's only 1.

pipe.named_steps

Then we fit a linear regression model to our data.

mexico_city1["surface_covered_in_m2"] = mexico_city1["surface_covered_in_m2"].astype(

print(y_pred.head())

<font size="+1">Practice</font>

print(y_pred.head())

Let's use the `make_pipeline` function to create a pipeline to fit a linear regression model for the `mexico-city-real-estate-1.csv` dataset.

X = mexico_city1.surface_covered_in_m2.values.reshape(-1, 1)

Let's try to predict `price_aprox_usd` in the `mexico-city-test-features.csv` dataset.

mexico_city_features = pd.read_csv("./data/mexico-city-test-features.csv")

## Accessing an Object in a Pipeline

Let's figure out the regression coefficients.

pipe.named_steps["regressor"].coef_

<font size="+1">Practice</font>

# INCLUDE pipe.named_steps[...].intercept_

*References & Further Reading*

---

xxxxxxxxxx​

xxxxxxxxxx<font size="+3"><strong>Visualizing Data: plotly express</strong></font>

xxxxxxxxxxThere are many ways to interact with data, and one of the most powerful modes of interaction is through **visualizations**. Visualizations show data graphically, and are useful for exploring, analyzing, and presenting datasets. We use four libraries for making visualizations: [pandas](../%40textbook/07-visualization-pandas.ipynb), [Matplotlib](../%40textbook/06-visualization-matplotlib.ipynb), plotly express, and [seaborn](../%40textbook/09-visualization-seaborn.ipynb). In this section, we'll focus on using plotly express.

xxxxxxxxxx# Scatter Plots

xxxxxxxxxxA **scatter plot** is a graph that uses dots to represent values for two different numeric variables. The position of each dot on the horizontal and vertical axis indicates values for an individual data point. Scatter plots are used to observe relationships between variables, and are especially useful if you're looking for **correlations**.

mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")

xxxxxxxxxxAfter cleaning the data, we can use plotly express to draw scatter plots by specifying the DataFrame and the interested column names.

fig = px.scatter(mexico_city1, x="price", y="surface_covered_in_m2")

xxxxxxxxxx<font size="+1">Practice</font>

fig = px.scatter(mexico_city1,x="price",y="surface_covered_in_m2")

xxxxxxxxxx# 3D Scatter Plots

] = mexico_city1["place_with_parent_names"].str.split("|", 4, expand=True)

/tmp/ipykernel_659/3122900234.py:11: FutureWarning:

In a future version of pandas all arguments of StringMethods.split except for the argument 'pat' will be keyword-only.

xxxxxxxxxxNotice that the plot is interactive: you can rotate it zoom in or out. These kinds of plots also makes outliers easier to find; here, we can see that houses have higher prices than other types of properties.

xxxxxxxxxx<font size="+1">Practice</font>

import math

xxxxxxxxxx# Mapbox Scatter Plots

xxxxxxxxxxA **mapbox scatter plot** is a special kind of scatter plot that allows you to create scatter plots in two dimensions and then superimpose them on top of a map. Our `mexico-city-real-estate-1.csv` dataset is a good place to start, because it includes **location data**. After importing the dataset and removing rows with missing data, split the `lat-lon` column into two separate columns: one for `latitude` and the other for `longitude`. Then use these to make a mapbox plot. Unfortunately, at present this type of plot does not easily allow for marker shape to vary based on a column of the DataFrame.

mexico_city1[["latitude", "longitude"]] = mexico_city1["lat-lon"].str.split(

/tmp/ipykernel_659/3692783844.py:6: FutureWarning:

In a future version of pandas all arguments of StringMethods.split except for the argument 'pat' will be keyword-only.

xxxxxxxxxx<font size="+1">Practice</font>

from math import log10

xxxxxxxxxx# Choropleth Maps

xxxxxxxxxxA Choropleth Map is a map composed of colored polygons, showing the variable of interest at different color depth across geographies.Plotly express has a function called `px.choropleth` that be used to plot Choropleth Map. The challenges here are getting the geometry information. There are two ways, one is to use the built-in geometries in plotly when plot US States (use the state name directly) and world countries (use ISP-3 code). Another way is to look for GeoJSON files where each location has geometry information. In the following example, we will show the plot in US States with a synthetic data set.

    {"State": ["CA", "TX", "NY", "HI", "DE"], "Temparature": [100, 120, 110, 90, 105]}

    df, locations="State", locationmode="USA-states", color="Temparature", scope="usa"

xxxxxxxxxx# Histogram

xxxxxxxxxxA **histogram** is a graph that shows the frequency distribution of numerical data. In addition to helping us understand frequency, histograms are also useful for detecting outliers. We can use the `px.histogram()` function from Plotly to draw histograms for specific columns, as long as the data type is numerical. Let's check the following example:

df = pd.read_csv("data/mexico-city-real-estate-1.csv")

xxxxxxxxxx<font size="+1">Practice</font>

fig = px.histogram(df,x="surface_covered_in_m2")

xxxxxxxxxx# Boxplots

xxxxxxxxxxA **boxplot** is a graph that shows the minimum, first quartile, median, third quartile, and the maximum values in a dataset. Boxplots are useful because they provide a visual summary of the data, enabling researchers to quickly identify mean values, the dispersion of the data set, and signs of skewness. In the following example, we will explore how to draw boxplots for specific columns of a DataFrame.

mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")

xxxxxxxxxxCheck the boxplot for column `"price"`:

fig = px.box(mexico_city1, y="price")

xxxxxxxxxxIf you want to check the distribution of a column value by different categories, defined by another categorical column, you can add an `x` argument to specify the name of the categorical column. In the following example, we check the price distribution across different property types:

fig = px.box(mexico_city1, x="property_type", y="price")

xxxxxxxxxx<font size="+1">Practice</font>

fig.show()

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In [15], line 2
      1 fig = ...
----> 2 fig.show()

AttributeError: 'ellipsis' object has no attribute 'show'

xxxxxxxxxx# Bar Chart

xxxxxxxxxxA **bar chart** is a graph that shows all the values of a categorical variable in a dataset. They consist of an axis and a series of labeled horizontal or vertical bars. The bars depict frequencies of different values of a variable or simply the different values themselves. The numbers on the y-axis of a vertical bar chart or the x-axis of a horizontal bar chart are called the scale.

] = mexico_city1["place_with_parent_names"].str.split("|", 4, expand=True)

xxxxxxxxxxWe can calculate the number of real estate showing in the data set by Borough using `value_counts()`, then plot it as bar plot:

mexico_city1["Borough"].value_counts()

fig = px.bar(mexico_city1["Borough"].value_counts())

xxxxxxxxxxWe can plot more expressive bar plots by adding more arguments. For example, we can plot the number of observations by borough and property type. First of all, we need use `groupby` to calculate the aggregated counts for each Borough and property type combination:

size_df = mexico_city1.groupby(["Borough", "property_type"], as_index=False).size()

xxxxxxxxxxBy specifying `x`, `y` and `color`, the following bar graph shows the total counts by Borough, with different property types showing in different colors. Note `y` has to be numerical, while `x` and `color` are usually categorical variables.<span style='color: transparent; font-size:1%'>WQU WorldQuant University Applied Data Science Lab QQQQ</span>

fig = px.bar(size_df, x="Borough", y="size", color="property_type", barmode="relative")

xxxxxxxxxxNote the argument `barmode` is specified as 'relative', which is also the default value. In this mode, bars are stacked above each other. We can also use 'overlay' where bars are drawn on top of each other.

fig = px.bar(size_df, x="Borough", y="size", color="property_type", barmode="overlay")

xxxxxxxxxxIf we want bars to be placed beside each other, we can specify `barmode` as "group":

fig = px.bar(size_df, x="Borough", y="size", color="property_type", barmode="group")

xxxxxxxxxx<font size="+1">Practice</font>

bar_df = ...

xxxxxxxxxx# References and Further Reading

xxxxxxxxxx---

​x

<font size="+3"><strong>Databases: PyMongo</strong></font>

# Working with PyMongo

from pprint import PrettyPrinter

## Databases

## Servers and Clients

client = MongoClient(host="localhost", port=27017)

## Semi-structured Data

## Exploring a Database

pp.pprint(list(client.list_databases()))

[ {'empty': False, 'name': 'admin', 'sizeOnDisk': 40960},
  {'empty': False, 'name': 'air-quality', 'sizeOnDisk': 7000064},
  {'empty': False, 'name': 'config', 'sizeOnDisk': 12288},
  {'empty': False, 'name': 'local', 'sizeOnDisk': 73728},
  {'empty': False, 'name': 'wqu-abtest', 'sizeOnDisk': 585728}]

It looks like this server contains four databases: `"admin"`, `"air-quality"`, `"config"`, and `"local"`. We're only interested in `"air-quality"`, so let's connect to that one:

db = client["air-quality"]

In MongoDB, a **database** is a container for **collections**. Each database gets its own set of files, and a single MongoDB **server** typically has multiple databases.

## Collections

for c in db.list_collections():

system.views
lagos
system.buckets.lagos
nairobi
system.buckets.nairobi
dar-es-salaam
system.buckets.dar-es-salaam

As you can see, there are three actual collections here: `"nairobi"`, `"lagos"`, and `"dar-es-salaam"`. Since we're only interested in the `"lagos"` collection, let's get it on its own like this:

lagos = db["lagos"]

## Documents

A MongoDB **document** is an individual record of data in a **collection**, and is the basic unit of analysis in MongoDB. Documents come with **metadata** that helps us understand what the document is; we'll get back to that in a minute. In the meantime, let's use the [`count_documents`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.collection.Collection.count_documents) method to see how many documents the `"lagos"` collection contains:

lagos.count_documents({})

166496

<font size="+1">Practice</font>

client = MongoClient(host = "localhost", port = 27017)

[    {'empty': False, 'name': 'admin', 'sizeOnDisk': 40960},
     {'empty': False, 'name': 'air-quality', 'sizeOnDisk': 7000064},
     {'empty': False, 'name': 'config', 'sizeOnDisk': 12288},
     {'empty': False, 'name': 'local', 'sizeOnDisk': 73728},
     {'empty': False, 'name': 'wqu-abtest', 'sizeOnDisk': 585728}]
system.views
lagos
system.buckets.lagos
nairobi
system.buckets.nairobi
dar-es-salaam
system.buckets.dar-es-salaam

202212

### Retrieving Data

result = lagos.find_one({})

{    '_id': ObjectId('6334b0f18c51459f9b1d955d'),
     'metadata': {    'lat': 6.501,
                      'lon': 3.367,
                      'measurement': 'temperature',
                      'sensor_id': 10,
                      'sensor_type': 'DHT11',
                      'site': 4},
     'temperature': nan,
     'timestamp': datetime.datetime(2018, 1, 7, 7, 7, 3, 88000)}

### Key-Value Pairs

### Metadata

### Identifiers

<font size="+1">Practice</font>

result = nairobi.find_one({})

{    'P1': 39.67,
     '_id': ObjectId('6334b0e98c51459f9b198d27'),
     'metadata': {    'lat': -1.3,
                      'lon': 36.785,
                      'measurement': 'P1',
                      'sensor_id': 57,
                      'sensor_type': 'SDS011',
                      'site': 29},
     'timestamp': datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)}

## Analyzing Data

lagos.distinct("metadata.site")

[3, 4]

Notice that in order to grab the `"site"` number, we needed to include the `"metadata"` tag.

print("Documents from site 3:", lagos.count_documents({"metadata.site": 3}))

Documents from site 3: 140586
Documents from site 4: 25910

<font size="+1">Practice</font>

print("Documents from site 29:", nairobi.count_documents({"metadata.site":29}))

Documents from site 29: 131852
Documents from site 6: 70360

print("Documents from site 29:", nairobi.count_documents({"metadata.site": 29}))

Documents from site 29: 131852
Documents from site 6: 70360

Now that we know how many *documents* are associated with each site, let's keep drilling down and find the number of *readings* for each site. We'll do this with the [`aggregate`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.collection.Collection.aggregate) method.

    [{"$group": {"_id": "$metadata.site", "count": {"$count": {}}}}]

[{'_id': 3, 'count': 140586}, {'_id': 4, 'count': 25910}]

With that information in mind, we might want to know what those readings actually are. Since we're really interested in measures of air quality, let's take a look at the `P2` values in the `"lagos"` collection. `P2` measures the amount of particulate matter in the air, which in this case is something called PM 2.5. If we wanted to get all the documents in a collection, we could, but that would result in an unmanageably large number of records clogging up the memory on our machines. Instead, let's use the [`find`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.collection.Collection.find) method and use `limit` to make sure we only get back the first 3.

result = lagos.find({"metadata.measurement": "P2"}).limit(3)

[    {    'P2': 14.42,
          '_id': ObjectId('6334b0f28c51459f9b1de145'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 7, 3, 39000)},
     {    'P2': 19.66,
          '_id': ObjectId('6334b0f28c51459f9b1de146'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 11, 23, 870000)},
     {    'P2': 24.79,
          '_id': ObjectId('6334b0f28c51459f9b1de147'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 21, 53, 981000)}]

<font size="+1">Practice</font>

    [{"$group": {"_id": "$metadata.site", "count": {"$count": {}}}}]

[{'_id': 6, 'count': 70360}, {'_id': 29, 'count': 131852}]
[    {    'P2': 14.42,
          '_id': ObjectId('6334b0f28c51459f9b1de145'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 7, 3, 39000)},
     {    'P2': 19.66,
          '_id': ObjectId('6334b0f28c51459f9b1de146'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 11, 23, 870000)},
     {    'P2': 24.79,
          '_id': ObjectId('6334b0f28c51459f9b1de147'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 21, 53, 981000)}]

So far, we've been dealing with relatively small subsets of the data in our collections, but what if we need to work with something bigger? Let's start by using `distinct` to remind ourselves of the kinds of data we have at our disposal.

lagos.distinct("metadata.measurement")

['humidity', 'temperature', 'P1', 'P2']

There are also comparison query operators that can be helpful for filtering the data. In total, we have

result = nairobi.find({"timestamp": {"$gt": datetime.datetime(2018, 9, 1)}}).limit(3)

[    {    'P1': 39.67,
          '_id': ObjectId('6334b0e98c51459f9b198d27'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)},
     {    'P1': 39.13,
          '_id': ObjectId('6334b0e98c51459f9b198d28'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 5, 3, 941000)},
     {    'P1': 30.07,
          '_id': ObjectId('6334b0e98c51459f9b198d29'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 10, 4, 374000)}]

result = nairobi.find({"timestamp": {"$lt": datetime.datetime(2018, 12, 1)}}).limit(3)

[    {    'P1': 39.67,
          '_id': ObjectId('6334b0e98c51459f9b198d27'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)},
     {    'P1': 39.13,
          '_id': ObjectId('6334b0e98c51459f9b198d28'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 5, 3, 941000)},
     {    'P1': 30.07,
          '_id': ObjectId('6334b0e98c51459f9b198d29'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 10, 4, 374000)}]

    {"timestamp": {"$eq": datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)}}

[    {    'P1': 39.67,
          '_id': ObjectId('6334b0e98c51459f9b198d27'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)},
     {    'P2': 34.43,
          '_id': ObjectId('6334b0ea8c51459f9b1a0db2'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P2',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)}]

<font size="+1">Practice</font>

result = nairobi.find({"timestamp":{"$gte":datetime.datetime(2018,12,1,0,0,6,767000)}}).limit(3)

[    {    'P1': 17.08,
          '_id': ObjectId('6334b0e98c51459f9b19eba8'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 12, 1, 0, 0, 6, 767000)},
     {    'P1': 17.62,
          '_id': ObjectId('6334b0e98c51459f9b19eba9'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 12, 1, 0, 5, 6, 327000)},
     {    'P1': 11.05,
          '_id': ObjectId('6334b0e98c51459f9b19ebaa'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 12, 1, 0, 10, 5, 579000)}]

# Less than or equal to

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In [23], line 5
      1 # Less than or equal to
      3 result = ...
----> 5 pp.pprint(list(result))

TypeError: 'ellipsis' object is not iterable

## Updating Documents

We can also update documents by passing some filter and new values using `update_one` to update one record or `update_many` to update many records. Let's look at an example. Before updating, we have this record showing like this:

    {"timestamp": {"$eq": datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)}}

Now we are updating the sensor type from `"SDS011"` to `"SDS"`, we first select all records with sensor type equal to `"SDS011"`, then set the new value to `"SDS"`:

    {"metadata.sensor_type": {"$eq": "SDS101"}},

Now we can see all records have changed:

    {"timestamp": {"$eq": datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)}}

We can change it back:

    {"$set": {"metadata.sensor_type": "SDS101"}},

result.raw_result

## Aggregation

Since we're looking for *big* numbers, we need to figure out which one of those dimensions has the largest number of measurements by **aggregating** the data in each document. Since we already know that `site 3` has significantly more documents than `site 2`, let's start looking at `site 3`. We can use the `$match` syntax to only select `site 3` data. The code to do that looks like this:

        {"$group": {"_id": "$metadata.measurement", "count": {"$count": {}}}},

<font size="+1">Practice</font>

pp.pprint(list(result))

After aggregation, there is another useful operator called `$project`, which allows you to specify which fields to display by adding new fields or deleting fields. Using the Nairobi data from site 29, we can first count each sensor type:<span style='color: transparent; font-size:1%'>WQU WorldQuant University Applied Data Science Lab QQQQ</span>

        {"$group": {"_id": "$metadata.sensor_type", "count": {"$count": {}}}},

We can see there are two sensor types and the corresponding counts. If we only want to display what are the types but do not care about the counts, we can suppress the `count` filed by setting it at 0 in `$project`:

        {"$group": {"_id": "$metadata.sensor_type", "count": {"$count": {}}}},

The `$project` syntax is also useful for deleting the intermediate fields that we used to generate our final fields but no longer need. In the following example, let's calculate the date difference for each sensor type. We'll first use the aggregation method to get the start date and last date.

                "date_min": {"$min": "$timestamp"},

Then we can calculate the date difference using `$dateDiff`, which gets the date difference through specifying the start date, end date and unit for timestamp data. We can see from the results above that the dates, are very close to each other. The only differences are in the minutes, so we can specify the unit as minute to show the difference. Since we don't need the start date and end dates, we can define a `"dateDiff"` field inside `$project`, so that it will be shown in the final display:

                "date_min": {"$min": "$timestamp"},

If we specify unit as `day`, it will show the difference between the dates:

                "date_min": {"$min": "$timestamp"},

<font size="+1">Practice</font>

pp.pprint(list(result))

We can do more with the date data using `$dateTrunc`, which truncates datetime data. We need to specify the datetime data, which can be a `Date`, a `Timestamp`, or an `ObjectID`. Then we need to specify the `unit` (year, month, day, hour, minute, second) and `binSize` (numerical variable defining the size of the truncation). Let's check the example below, where we group data by the month using `$dateTrunc` and then count how many observations there are for each month.

                            "date": "$timestamp",

<font size="+1">Practice</font>

pp.pprint(list(result))

## Finishing Up

    # `projection` limits the kinds of data we'll get back.

Finally, we'll use pandas to read the extracted data into a DataFrame, making sure to set `timestamp` as the index:

df = pd.DataFrame(result).set_index("timestamp")

<font size="+1">Practice</font>

result = ...

# References & Further Reading

---

​x

<font size="+3"><strong>Machine Learning: Classification</strong></font>

warnings.simplefilter(action="ignore", category=FutureWarning)

# Data Preprocessing

For the examples here, we'll look at buildings in the Ramechhap district of Nepal. (In our SQLite database, Ramechhap has the `district_id` of `1`.) Run the wrangle function below to connect to the SQLite database load the data into the DataFrame `df`.

    df["damage_grade"] = pd.to_numeric(df["damage_grade"], errors="coerce")

df.head()

# Data Segregation

## Training Sets

### Randomized Train-Test split

**Splitting a dataset** into different sets is an important part of the model development process. The initial dataset is typically split into **two** (**training** and **testing**) or **three** (**training**, **validation**, and **testing**) datasets. This helps ensure that the model can generalize. Usually, more data is used for training than for validation or testing. If splitting into two datasets, a good rule of thumb is to split your data randomly into a ratio of **80:20** training:testing. If splitting into three datasets, splitting the data into a ratio of **70:20:10** (training:validation:testing) is commonly used.

target = "severe_damage"

Drop the target from the DataFrame and save the results into a X. Save the target column into y.

X = ...

Finally, we will split our dataset into a training and test set using the `train_test_split` function from `scikit-learn`.

X_train, X_test, y_train, y_test = train_test_split(

## Validation Set

<font size="+1">Practice: Perform a randomized split using scikit-learn</font>

X_train, X_val, y_train, y_val = ...

# Key Concepts

## Majority and Minority Classes

The majority class refers to whatever category in a binary target occurs most frequently, and the minority class refers to whatever category in a binary target occurs less frequently. Let's use the `value_counts` method to plot the relative frequency of the two plots with a bar chart.

    kind="bar", xlabel="Group", ylabel="Relative Frequency"

Since the category 1 (`severe_damage` = True) occurs most frequently, this is the majority class.

## Positive and Negative Classes

**Positive class** and **negative class** are the two possible labels for binary classification problems. For example, if we are classifying whether an email is spam or not, we can designate "spam" as the positive class and "not spam" as the negative class. For the example in the project, we have "bankrupt" as the positive class and "not bankrupt" as the negative class. Conventionally, we use `0` or `False` to represent negative class, and `1` or `True` to represent positive class.

# Classification with Logistic Regression

## Logistic Regression

x = np.linspace(-10, 10, 100)

The following video summarizes the math behind logistic regression:

YouTubeVideo("yIYKR4sgzI8")

You can add the logistic regression as a named step in a model pipeline like below:

model = make_pipeline(OneHotEncoder(), LogisticRegression(max_iter=1000))

## High-cardinality Features

Cardinality refers to the number of unique values in a categorical variable. High cardinality means the categorical features have a large number of unique values. These features often don't work well with either one hot encoding or ordinal encoding. There is no exact number of unique values that makes a feature high-cardinality, but if the value of the categorical feature is unique for almost all observations, it can usually be dropped. You can see the number of unique values in a variable by using the `value_counts` method. For example, to check the number of unique values in the `roof_type` column:

df["roof_type"].value_counts()

There are only three unique values, so we will leave the column in the DataFrame.

# X_train["building_id"].value_counts()

# Classification with Tree-based Models

## Decision Trees

Decision trees are a general class of machine learning models that are used for both classification and regression. The model resemble a tree, complete with branches and leaves. The model is essentially a series of questions with "yes" or "no" answers. The decision tree starts by checking whatever condition does the best job at correctly separating the data into the two classes in the binary target. It then progressively checks more conditions until it can predict an observation's label. They are popular because they are more flexible than linear models and intuitive in a way that makes them easy to explain to stakeholders who are not familiar with data science.

Decision trees pros and cons:

The following video summarizes a decision tree:

YouTubeVideo("7VeUPuFGJHk")

We will fit a decision tree to the training data, using an ordinal encoder to encode the categorical features:

    OrdinalEncoder(), DecisionTreeClassifier(max_depth=6, random_state=42)

## Prediction

## Probability Estimates

Sometimes a model makes the same prediction for the target of two observations, but is more certain about one prediction. This is the difference between the prediction and the prediction's associated probability.

model.predict(X_train)

And if we wanted to see the associated probabilities for these predictions:

model.predict_proba(X_train)

Note that there are two probability estimates for each observation, one for the likelihood of each class in the target. The second probability is the likelihood that the unknown observation belongs to the class equal to 1 and the first probability is the likelihood that the unknown observation belongs to the class equal to 0. Whichever class's probability is higher is the predicted class from `predict`.

<font size="+1">Practice: Generate probability estimates using a trained model in scikit-learn</font>

model.predict_proba(X_test)

## Evaluation

### Calculating Accuracy Score

A natural choice for a metric for classification is accuracy. Accuracy is equal to the number of observations you correctly classified over all observations. For example, if your model properly identified 77 out of 100 images, you have an accuracy of 77%. Accuracy is an easy metric to both understand and calculate. Mathematically, it is simply

acc_train = accuracy_score(y_train, model.predict(X_train))

<font size="+1">Practice: Calculate the accuracy score for a model in scikit-learn</font>

print("Validation Accuracy:", round(acc_val, 2))

### Baseline Accuracy Score

acc_baseline = y_train.value_counts(normalize=True).max()

### Confusion Matrix

Accuracy score may not provide enough information to assess how a model is performing because it only gives us an overall score. Also, imbalanced data can lead to a high accuracy score even when a model isn't particularly useful. If we want to know what fraction of all positive predictions were correct and what fraction of positive observations did we identify, we can use a **confusion matrix**.

Refer to this video for more details in confusion matrix:

YouTubeVideo("_cpiuMuFj3U")

Here is the code to get the confusion matrix in the training set:

cm = confusion_matrix(y_train, model.predict(X_train))

You can also use the heatmap to better visualize confusion matrix using `ConfusionMatrixDisplay`:

disp = ConfusionMatrixDisplay(confusion_matrix=cm, display_labels=model.classes_)

<font size="+1">Practice</font>

disp.plot()

### Precision Score

Depending on the context of the problem, instead of knowing model performances in both classes, sometimes we are more interested in the results in positive class. That's when we use **precision**. Precision is the fraction of true positives over all positive predictions. It is a measure of how "precise" our model is with regard to labeling observations as positive.

Using the data and model above, we can get a precision score using the code below:

precision = precision_score(y_train, model.predict(X_train))

<font size="+1">Practice</font>

print(f"Validation Set Precision is {round(precision_val, 2)}")

### Recall Score

What if we care more about the model performance in the negative class? In this case, we need to calculate **recall**. Recall the fraction of true positives over all positive observations. It is a measure of our model's ability to "catch" and properly label observations that are positive.

Here is the code to calculate recall:

recall = recall_score(y_train, model.predict(X_train))

<font size="+1">Practice</font>

print(f"Validation Set Precision is {round(recall_val, 2)}")

### Classification Report

We can also use a **classification report** to look at the whole picture of the classification model performances. A classification report includes precision, recall, **F1 score** and **support**. We already know the first two, but F1 score is the harmonic mean of precision and recall, it equation is:

print(classification_report(y_train, model.predict(X_train)))

Note in the last two rows, we have the macro and the weighted average,. Macro average is the arithmetic average of a metric between the two classes:

Here you may notice there are two rows of metrics. If you refer back to what we calculated previous on precision and recall, the second row actually align with what we found. That's because we usually define class one as the **positive class**, thus we are referring class 1's metric performance as the true precision and recall value.

<font size="+1">Practice</font>

print(...)

## Communication

### Plotting a Decision Tree

The `plot_tree` function can be used to a plot a decision tree. The visualization is fit to the size of the axis set with `matplotlib`. Use the `figsize` argument of `plt.subplots` to control the size of the tree.

We'll demonstrate how to use the `plot_tree` function to graphically display a decision tree:

    proportion=True,  # Display proportion of classes in leaf

<font size="+1">Practice: Plot a decision tree using `scikit-learn`</font>

fig, ax = plt.subplots(figsize=(25, 12))

The feature names and importance of features can be extracted from the column names in your training set. For the `importances`, you can access the [`feature_importances_`](https://scikit-learn.org/stable/modules/generated/sklearn.tree.DecisionTreeClassifier.html#sklearn.tree.DecisionTreeClassifier.feature_importances_) attribute of your model's `DecisionTreeClassifier`.

importances = model.named_steps["decisiontreeclassifier"].feature_importances_

The importance of a feature is based on how well the feature correctly classifies observations. In a decision tree, this is on average how much a feature reduces the impurity metric. The tree determines how to split based on an impurity function. The impurity function calculates how homogeneous observations are at a particular leaf node. Conditions that do a better job minimizing impurity are used to split first. The `sklearn.tree` algorithm uses the Gini impurity by default. The Gini impurity measures the probability of an incorrect classification in the model for each branch. It ranges from 0 to 1.

Let's create a bar chart to plot each feature with its corresponding importance. To build this bar chart, we'll create a pandas Series named feat_imp, where the index is features and the values are your importances. The Series should be sorted from smallest to largest importance so that the bar chart is also in order.

feat_imp = pd.Series(importances, index=features).sort_values()

Next, we'll use the series to build a bar chart:

plt.xlabel("Gini Importance");

# Classification with Ensemble Models

Ensemble models are machine learning models that use more than one predictor to arrive at a prediction. A group of predictors form an _ensemble_. In general, ensemble models perform better than using a single predictor. There are three types of ensemble models: **bagging**, **boosting**, and **blending**. Of the three, decision trees are commonly used to construct bagging and boosting models.

## Random Forest

The performance of a single decision tree will be limited. Instead of relying on one tree, a better approach is to aggregate the predictions of multiple trees. On average, aggregation will perform better than a single predictor. You can envision the aggregation as mimicking the idea of "wisdom of the crowd." We call a tree based model that aggregates the predictions of multiple trees a **random forest**.

## Gradient Boosting Trees

Gradient boosting trees is another ensemble model. It uses a collection of tree models arranged in a sequence. Here, the model is built stage-wise; each additional tree aims to correct the previous tree's incorrect.

Gradient boosting trees have a similar set of hyperparameters as random forests but with some key additions.

The learning rate determines how much each tree affect the final outcome and is very important in model convergence. Thus it should be considered during hyperparameter tuning to improve model performance.

# Hyperparameter Tuning

When we defined our decision tree estimator, we chose how many layers the tree would have using the `max_depth` argument. More generally, when we instantiate any estimator, we can pass keyword arguments that will dictate its structure. The decision tree regressor accepts [12 different keyword arguments](http://scikit-learn.org/stable/modules/generated/sklearn.tree.DecisionTreeRegressor.html#sklearn.tree.DecisionTreeRegressor). These arguments are called **hyperparameters**. This is in contrast to **parameters**, which are the numbers that our model uses to predict labels based on features. Hyperparameters are decided before training and dictate the model's structure. Parameters are optimized during training. Basically all models have hyperparameters. Even a simple linear regressor has a hyperparameter: `fit_intercept`.

## Cross-Validation

When trying out different hyperparameter settings for estimators (such as the `max_depth` for a random forest), there's a risk in using the test set to evaluate these settings. What happens is that your knowledge about the test set can “leak” into the model, and performance metrics no longer reflect the model's ability to generalize.

Here is the code for conducting a 5-fold cross-validation and reporting the accuracy score for each fold.

clf = make_pipeline(OneHotEncoder(), DecisionTreeClassifier())

    f"{round(scores.mean(),2)} accuracy with a standard deviation of {round(scores.std(),2)}"

<font size="+1">Practice</font>

scores = ...

## Grid Search

Another a useful tool for comparing different hyperparameter values is `GridSearchCV`. There are two ideas behind `GridSearchCV`: first we split the data using k-fold cross-validation, and then we train and evaluate models with different hyperparameter settings selected from a grid of possible combinations.

First, we need to define the hyperparameters we want to tune, and tuning in what range. Here we are using an example of searching the best value for the `max_depth` in decision tree model. Since we will be building a pipeline including a transformer and estimator, we need to specify `max_depth` comes from the estimator `decisiontreeclassifier`.

params = {"decisiontreeclassifier__max_depth": range(1, 15)}

The we define the pipeline and the model with `GridSearchCV`.

clf = make_pipeline(OneHotEncoder(), DecisionTreeClassifier())

Lastly fit the model:

model.fit(X_train, y_train)

We can check the best parameters once the fitting process finished:

model.best_params_

<font size="+1">Practice</font>

# Check the best hyperparameters

# References & Further Reading

---

​x

<font size="+3"><strong>Machine Learning: Core Concepts</strong></font>

# Model Types

**Linear regression** is a way to predict the value of some a target variable by fitting a line that best describes the relationship between **Big X** and **little y** for the values we already have. If you remember `y = mx + b` from Algebra, it's the same thing; the `y` is the intercept, and the `b` is the beta coefficient. The beta coefficient tells us what change we can expect to see in `X` for every one-unit increase in `y`. If that doesn't seem familiar to you, don't worry about it; we'll give you everything you need to know.

# Statistical Concepts

## Cost Functions

## Residuals

## Performance Metrics

# Data Concepts

## Leakage

## Imputation

## Generalization

# Model Concepts

## Hyperparameters

When we instantiate an estimator, we can pass keyword arguments that will dictate its structure. These arguments are called **hyperparameters**. For example, when we defined our decision tree estimator, we chose how many layers the tree would have using the `max_depth` keyword. This is in contrast to **parameters**, which are the numbers that our model uses to make predictions based on features. Parameters are optimized during the training process based on data and input features. They keep changing during training to fit the data and only the best performed ones were selected.  Hyperparameters values are set before training begins and will not be changed during the training process. Pretty much all models have hyperparameters. Even a simple linear regressor has a hyperparameter: `fit_intercept`. Here are some common examples for Hyperparameters:

# References and Further Reading

- [Parameters and Hyperparameters in Machine Learning and Deep Learning](https://towardsdatascience.com/parameters-and-hyperparameters-aa609601a9ac)

---

---

​x

<font size="+3"><strong>Visualizing Data: seaborn</strong></font>

There are many ways to interact with data, and one of the most powerful modes of interaction is through **visualizations**. Visualizations show data graphically, and are useful for exploring, analyzing, and presenting datasets. We use four libraries for making visualizations: [pandas](../%40textbook/07-visualization-pandas.ipynb), [Matplotlib](../%40textbook/06-visualization-matplotlib.ipynb), [plotly express](../%40textbook/08-visualization-plotly.ipynb), and seaborn. In this section, we'll focus on using seaborn.

# Scatter Plots

A **scatter plot** is a graph that uses dots to represent values for two different numeric variables. The position of each dot on the horizontal and vertical axis indicates values for an individual data point. Scatter plots are used to observe relationships between variables, and are especially useful if you're looking for **correlations**.

mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")

Use seaborn to plot the scatter plot for `"price"` and `"surface_covered_in_m2"`:

sns.scatterplot(data=mexico_city1, x="price", y="surface_covered_in_m2");

There is a very useful argument in `scatterplot` called `hue`. By specifying a categorical column as `hue`, seaborn can create a scatter plot between two variables in different categories with different colors. Let's check the following example using `"property_type"`:

    data=mexico_city1, x="price", y="surface_covered_in_m2", hue="property_type"

<font size="+1">Practice</font>

​x

# Bar Charts

A **bar chart** is a graph that shows all the values of a categorical variable in a dataset. They consist of an axis and a series of labeled horizontal or vertical bars. The bars depict frequencies of different values of a variable or simply the different values themselves. The numbers on the y-axis of a vertical bar chart or the x-axis of a horizontal bar chart are called the scale.

] = mexico_city1["place_with_parent_names"].str.split("|", 4, expand=True)

/tmp/ipykernel_721/836102575.py:12: FutureWarning: In a future version of pandas all arguments of StringMethods.split except for the argument 'pat' will be keyword-only.
  ] = mexico_city1["place_with_parent_names"].str.split("|", 4, expand=True)

Let's check the example of a bar plot showing the value counts of each borough in the dataset. We first need to create a DataFrame showing the value counts:

bar_df = pd.DataFrame(mexico_city1["Borough"].value_counts()).reset_index()

Since there are 16 different categories in Borough, we should increase the default plot size and rotate the x axis to make the plot more readable using the following syntax:

ax = sns.barplot(data=bar_df, x="index", y="Borough")

[Text(0, 0, 'Miguel Hidalgo'),
 Text(1, 0, 'Cuajimalpa de Morelos'),
 Text(2, 0, 'Álvaro Obregón'),
 Text(3, 0, 'Benito Juárez'),
 Text(4, 0, 'Tlalpan'),
 Text(5, 0, 'Iztapalapa'),
 Text(6, 0, 'Tláhuac'),
 Text(7, 0, 'Cuauhtémoc'),
 Text(8, 0, 'Gustavo A. Madero'),
 Text(9, 0, 'Venustiano Carranza'),
 Text(10, 0, 'Coyoacán'),
 Text(11, 0, 'La Magdalena Contreras'),
 Text(12, 0, 'Xochimilco'),
 Text(13, 0, 'Iztacalco'),
 Text(14, 0, 'Azcapotzalco'),
 Text(15, 0, 'Milpa Alta')]

<font size="+1">Practice</font>

pro_typ_df = pd.DataFrame(mexico_city1["property_type"].value_counts()).reset_index()

<AxesSubplot:xlabel='index', ylabel='property_type'>

# Correlation Heatmaps

mexico_city1_numeric = mexico_city1.select_dtypes(include="number")

<AxesSubplot:>

Notice that we dropped the columns and rows with missing entries before plotting the graph.

<font size="+1">Practice</font>

​x

# References and Further Reading

---

---

Magic Command	Description of Command
`%pwd`	Print the current working directory
`%cd`	Change the current working directory
`%ls`	List the contents of the current directory
`%history`	Show the history of the In [ ]: commands

Clause Name	Definition
`SELECT`	Determines which columns to include in the query's result
`FROM`	Identifies the table from which to query the data from
`WHERE`	filters data
`GROUP BY`	groups rows by common values in columns
`HAVING`	filters out unwanted groups from GROUP BY
`ORDER BY`	Orders the rows using one or more columns
`LIMIT`	Outputs the specified number of rows

Comparison Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<> or !=	Not equal to
LIKE	String comparison test

Aggregation Function	Definition
`MIN`	Return the minimum value
`MAX`	Return the largest value
`SUM`	Return the sum of values
`AVG`	Return the average of values
`COUNT`	Return the number of observations

JOIN Type	Definition
`INNER JOIN`	Returns rows where ID is in both tables
`LEFT JOIN`	Returns rows where ID is in the left table. Return NA for values in column, if ID is not in right table.
`RIGHT JOIN`	Returns rows where ID is in the right table. Return NA for values in column, if ID is not in left table.
`FULL JOIN`	Returns rows where ID is in either table. Return NA for values in column, if ID is not in either table.
WQU WorldQuant University Applied Data Science Lab QQQQ

	price	price_aprox_local_currency	price_aprox_usd	surface_total_in_m2	surface_covered_in_m2	price_per_m2
2	2700000.0	2748947.10	146154.51	61.0	61.0	44262.295082
3	6347000.0	6462061.92	343571.36	176.0	128.0	49585.937500
4	6870000.0	6994543.16	371882.03	180.0	136.0	50514.705882
5	6500000.0	6617835.61	351853.45	300.0	300.0	21666.666667
6	670000.0	682146.11	36267.97	65.0	65.0	10307.692308

	price	price_aprox_local_currency	price_aprox_usd	surface_total_in_m2	surface_covered_in_m2	price_per_m2
price	1.000000	0.333655	0.333655	0.112588	0.371073	0.380879
price_aprox_local_currency	0.333655	1.000000	1.000000	0.118123	0.598506	-0.068775
price_aprox_usd	0.333655	1.000000	1.000000	0.118123	0.598506	-0.068775
surface_total_in_m2	0.112588	0.118123	0.118123	1.000000	0.125032	0.003488
surface_covered_in_m2	0.371073	0.598506	0.598506	0.125032	1.000000	-0.147158
price_per_m2	0.380879	-0.068775	-0.068775	0.003488	-0.147158	1.000000

	surface_covered_in_m2	lat	lon	neighborhood
0	90.0	19.367931	-99.170262	Benito Juárez
1	50.0	19.363542	-99.224084	Álvaro Obregón
2	280.0	19.457982	-99.192690	Miguel Hidalgo
3	55.0	19.334270	-99.083374	Iztapalapa
4	80.0	19.416881	-99.109781	Venustiano Carranza

	operation	property_type	place_with_parent_names	lat-lon	price	currency	price_aprox_local_currency	price_aprox_usd	surface_total_in_m2	surface_covered_in_m2	price_usd_per_m2	price_per_m2	floor	rooms	expenses	properati_url
0	sell	apartment	\|México\|Distrito Federal\|Álvaro Obregón\|	NaN	35000000.0	MXN	35634500.02	1894595.53	NaN	NaN	NaN	NaN	NaN	NaN	NaN	http://alvaro-obregon.properati.com.mx/2eb_ven...
1	sell	apartment	\|México\|Distrito Federal\|Benito Juárez\|	NaN	2000000.0	MXN	2036257.11	108262.60	NaN	NaN	NaN	NaN	NaN	NaN	NaN	http://benito-juarez.properati.com.mx/2ec_vent...
2	sell	apartment	\|México\|Distrito Federal\|Cuauhtémoc\|	19.41501,-99.175174	2700000.0	MXN	2748947.10	146154.51	61.0	61.0	2395.975574	44262.295082	NaN	3.0	NaN	http://cuauhtemoc.properati.com.mx/2pu_venta_a...
3	sell	apartment	\|México\|Distrito Federal\|Cuauhtémoc\|	19.41501,-99.175174	6347000.0	MXN	6462061.92	343571.36	176.0	128.0	1952.110000	49585.937500	NaN	5.0	NaN	http://cuauhtemoc.properati.com.mx/2pv_venta_a...
4	sell	apartment	\|México\|Distrito Federal\|Álvaro Obregón\|	NaN	6870000.0	MXN	6994543.16	371882.03	180.0	136.0	2066.011278	50514.705882	NaN	5.0	NaN	http://alvaro-obregon.properati.com.mx/2pw_ven...

Pros	Cons
can be used for classification and regression	generalization: they are prone to overfitting
handles both numerical and categorical data	robustness: small variations in data can result in a different tree
models nonlinear relationships between the features and target	class imbalance: if one class is much larger than the other, the tree may be unbalanced

	Positive Observation	Negative Observation
Positive Prediction	True Positive (`TP`)	False Positive (`FP`)
Negative Prediction	False Negative (`FN`)	True Negative (`TN`)

Hyperparameter	Description
`n_estimators`	The number of trees in the forest
`max_samples`	If bootstrap is True, the number of samples to draw from X to train each base estimator
`max_features`	The number of features to consider when looking for the best split
`n_jobs`	The number of jobs to run in parallel when fitting and predicting
`warm_start`	If set to `True`, reuse the trained tree from a prior fitting and just train the additional trees

Hyperparameter	Description
`learning_rate`	Multiplicative factor of the tree's contribution to the model.
`subsample`	Fraction of the training data to use when fitting the trees.

	index	Borough
0	Miguel Hidalgo	345
1	Cuajimalpa de Morelos	255
2	Álvaro Obregón	203
3	Benito Juárez	198
4	Tlalpan	171
5	Iztapalapa	134
6	Tláhuac	125
7	Cuauhtémoc	120
8	Gustavo A. Madero	89
9	Venustiano Carranza	81
10	Coyoacán	80
11	La Magdalena Contreras	41
12	Xochimilco	34
13	Iztacalco	27
14	Azcapotzalco	24
15	Milpa Alta	1

	State	Temparature
0	CA	100
1	TX	120
2	NY	110
3	HI	90
4	DE	105

Open Tabs

Kernels

Terminals

Working with SQL Databases¶

Connecting to a Database¶

ipython-sql¶

Magic Commands¶

ipython-sql¶

Connecting with ipython-sql¶

sqlite3¶

Querying a Database¶

Building Blocks of the Basic Query¶

SELECT and FROM¶

Filtering and Sorting Data¶

Aggregating Data¶

Joining Tables¶

Using pandas with SQL Databases¶

References & Further Reading¶

Linear Regression¶

Fitting a Model to Training Data¶

Generating Predictions Using a Trained Model¶

Ridge Regression¶

Generalization¶

Calculating the Mean Absolute Error for a List of Predictions¶

Access an Attribute of a Trained Model¶

Multicollinearity¶

What's scikit-learn?¶

Data Preprocessing¶

Standardization¶

One-Hot Encoding¶

Ordinal Encoding¶

Imputation¶

Data Leakage¶

Imbalanced Data¶

Under-sampling¶

Over-sampling¶

Practice¶

scikit-learn in Production¶

Creating a Pipeline in scikit-learn¶

Accessing an Object in a Pipeline¶

Scatter Plots¶

3D Scatter Plots¶

Mapbox Scatter Plots¶

Choropleth Maps¶

Histogram¶

Boxplots¶

Bar Chart¶

References and Further Reading¶

Working with PyMongo¶

Databases¶

Servers and Clients¶

Semi-structured Data¶

Exploring a Database¶

Collections¶

Documents¶

Retrieving Data¶

Key-Value Pairs¶

Metadata¶

Identifiers¶

Analyzing Data¶

Updating Documents¶

Aggregation¶

Finishing Up¶

References & Further Reading¶

Data Preprocessing¶

Data Segregation¶

Training Sets¶

Randomized Train-Test split¶

Validation Set¶

Key Concepts¶

Majority and Minority Classes¶

Positive and Negative Classes¶

Classification with Logistic Regression¶

Logistic Regression¶

High-cardinality Features¶

Classification with Tree-based Models¶

Decision Trees¶

Prediction¶

Probability Estimates¶

Evaluation¶